-- EXEC SP_GET_REEL_QTY_DETAILS '0', 0

CREATE PROC SP_GET_REEL_QTY_DETAILS  ( @PACKAGE_NAME VARCHAR(50), @PINCOUNT INT ) AS
BEGIN
	
	DECLARE @REEL_QTY VARCHAR(10)
	DECLARE @PACKAGE VARCHAR(50)
	DECLARE @PIN_COUNT VARCHAR(50)
	DECLARE @RESULTS TABLE(PACKAGENAME VARCHAR(10), PINNUMBER INT,REELQTY INT)
	DECLARE @SQL_STRING VARCHAR(800)

	SET @SQL_STRING = 'DECLARE REEL_QTY_CURSOR CURSOR FOR SELECT PACKAGE, PIN_COUNT, REEL_QTY  FROM REEL_QTY_TABLE  WHERE 1 = 1  '
		
	IF ( @PACKAGE_NAME IS NOT NULL AND @PACKAGE_NAME != '0' )
	BEGIN
		SET @SQL_STRING = @SQL_STRING +'AND  PACKAGE =''' + @PACKAGE_NAME + ''''
	END
	IF ( @PINCOUNT != 0 )
	BEGIN
		SET @SQL_STRING =@SQL_STRING + ' AND PIN_COUNT = ' +  CONVERT(VARCHAR,@PINCOUNT )
	END	
	--PRINT @SQL_STRING
	EXEC (@SQL_STRING)

	OPEN REEL_QTY_CURSOR
	FETCH NEXT FROM REEL_QTY_CURSOR INTO @PACKAGE, @PIN_COUNT, @REEL_QTY
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @RESULTS ( PACKAGENAME , PINNUMBER ,REELQTY ) 
		VALUES (@PACKAGE, @PIN_COUNT, @REEL_QTY)
	SET @PACKAGE=NULL
    SET @PIN_COUNT=0
	SET @REEL_QTY=0
	FETCH NEXT FROM REEL_QTY_CURSOR INTO @PACKAGE, @PIN_COUNT, @REEL_QTY
	END
	CLOSE REEL_QTY_CURSOR
	DEALLOCATE REEL_QTY_CURSOR
	SELECT PACKAGENAME , PINNUMBER ,REELQTY FROM @RESULTS ORDER BY PACKAGENAME,PINNUMBER
END